﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Windows;
using StudentManagement.Entities;

namespace StudentManagement
{
    /// <summary>
    /// Interaction logic for FrmSubject.xaml
    /// </summary>
    public partial class FrmSubject
    {
        private readonly bool _isUpdate;

        public FrmSubject()
        {
            InitializeComponent();
            _isUpdate = false;
        }

        public FrmSubject(Subject subject)
        {
            InitializeComponent();
            PopulateData(subject);
            _isUpdate = true;
            txtCode.IsEnabled = false;
        }

        private void PopulateData(Subject subject)
        {
            try
            {
                txtCode.Text = subject.Subject_Code;
                txtName.Text = subject.Subject_Name;
                txtTerm.Text = subject.Term;
                txtAbbreviation.Text = subject.Subject_Abbreviation;
                txtNoCredit.Text = subject.No_Of_Credit.ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void btnClose_Click(object sender, RoutedEventArgs e)
        {
            Close();
        }

        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (_isUpdate)
                    Update();
                else
                    Add();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void Add()
        {
            OleDbConnection conn = null;
            try
            {
                try
                {
                    Convert.ToInt32(txtNoCredit.Text.Trim());
                }
                catch (Exception)
                {
                    MessageBox.Show("No. Of Credit accept number only.", "Error", MessageBoxButton.OK,
                                    MessageBoxImage.Error);
                    txtNoCredit.Focus();
                    return;
                }
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                // Build query
                String strCommand =
                    "INSERT INTO SubjectTable (Subject_Code, Subject_Name, Term, Subject_Abbreviation, No_Of_Credit)" +
                    " VALUES( "
                    + "'" + txtCode.Text.Trim() + "', "
                    + "'" + txtName.Text.Trim() + "', "
                    + "'" + txtTerm.Text.Trim() + "', "
                    + "'" + txtAbbreviation.Text.Trim() + "', "
                    + txtNoCredit.Text + " );";

                var cmd = new OleDbCommand(strCommand, conn);
                cmd.ExecuteNonQuery();

                // Add records to Marks table
                var lstStudent = GetStudentList();

                foreach (var student in lstStudent)
                {
                    String strCmd =
                    "INSERT INTO Student_Subject_ExamTable (Student_Code, Subject_Code, Exam_Seat_Time, Mark, Com_Exam)" +
                    " VALUES( "
                    + "'" + student.Student_Code + "', "
                    + "'" + txtCode.Text.Trim() + "', "
                    + 0 + ", "
                    + 0 + ", "
                    + "'' );";

                    var cmdMark = new OleDbCommand(strCmd, conn);
                    cmdMark.ExecuteNonQuery();
                }

                MessageBox.Show("Add Subject successfull.", "Infomation", MessageBoxButton.OK,
                                MessageBoxImage.Information);
                Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (conn != null) conn.Close();
            }
        }

        private void Update()
        {
            OleDbConnection conn = null;
            try
            {
                try
                {
                    Convert.ToInt32(txtNoCredit.Text.Trim());
                }
                catch (Exception)
                {
                    MessageBox.Show("No. Of Credit accept number only.", "Error", MessageBoxButton.OK,
                                    MessageBoxImage.Error);
                    txtNoCredit.Focus();
                    return;
                }

                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                String strCommand =
                    "UPDATE SubjectTable SET "
                    + "Subject_Name = '" + txtName.Text.Trim()
                    + "', Term = '" + txtTerm.Text.Trim()
                    + "', Subject_Abbreviation = '" + txtAbbreviation.Text.Trim()
                    + "', No_Of_Credit = " + txtNoCredit.Text.Trim()
                    + " WHERE Subject_Code = '" + txtCode.Text + "'";

                var cmd = new OleDbCommand(strCommand, conn);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Update Subject successfull.", "Infomation", MessageBoxButton.OK,
                                MessageBoxImage.Information);
                Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (conn != null) conn.Close();
            }
        }

        private static IEnumerable<Student> GetStudentList()
        {
            OleDbConnection conn = null;
            OleDbDataReader reader = null;

            try
            {
                var lstStudent = new List<Student>();
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                var cmd = new OleDbCommand("Select * FROM StudentTable", conn);
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {                   
                    while (reader.Read())
                    {
                        var std = new Student
                        {
                            Student_Code = reader.GetString(0),
                            Student_Name = reader.GetString(1),
                            Class_Code = reader.GetString(2),
                            Student_DOB = reader.GetDateTime(3),
                            Student_Sex = reader.GetBoolean(4),
                            Current_Address = reader.GetString(5),
                            Contact_Address = reader.GetString(6)
                        };

                        lstStudent.Add(std);
                    }
                }
                return lstStudent;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (reader != null) reader.Close();
                if (conn != null) conn.Close();
            }
        }
    }
}
